package com.cn.ccp.service.onlineEdu;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import net.sf.json.JSONObject;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;

import com.cn.ccp.bean.OnlineExamBean;
import com.cn.ccp.bean.StudentTrainingRecordBean;
import com.cn.ccp.bean.TrainingBean;
import com.cn.ccp.service.BasicService;
import com.cn.ccp.service.course.CourseService;
import com.cn.ccp.service.course.TrainingService.TrainingRowMapper;
import com.cn.ccp.util.CCPContants;
/**
 * 在线考试service
 * @author Administrator
 *
 */
public class OnlineExamService extends BasicService {
	private Log log = LogFactory.getLog(CourseService.class);
	
	public boolean saveSelectValueChange(final List<OnlineExamBean> list){
		final int index = CCPContants.getClobalIndex() ;
		transactionTemplate.execute(new TransactionCallback() {
			public Object doInTransaction(TransactionStatus arg0) {
				try {
					String sql = "DELETE FROM student_training_record WHERE selected_course_id = ?";
					jdbcTemplate.update(sql ,new Object[]{list.get(0).getSelectCourseId()});
					for(OnlineExamBean bean : list ){
						if(bean.getSelectAnswer() != null  && !bean.getSelectAnswer().equals("")){
							//写入临时表中记录选择信息
							sql = "INSERT INTO student_training_record (selected_course_id, traing_id,answer) VALUES (?, ?,?)";
							jdbcTemplate.update(sql ,new Object[]{bean.getSelectCourseId() ,bean.getId() , bean.getSelectAnswer()});
							System.err.println(bean.getId() +"--"+bean.getChapter_course_id() +"----"+bean.getSelectAnswer());
						}
					}
					CCPContants.tranmitResultMap.put(index, true);
				} catch (Exception e) {
					arg0.setRollbackOnly();
					log.error("selectValueChange failed : ", e);
					CCPContants.tranmitResultMap.put(index, false);
				}
				return null;
			}
		});
		return (Boolean) CCPContants.tranmitResultMap.get(index);
	}
	
	
	public List<StudentTrainingRecordBean> getStudentTrainingRecordList(String selected_course_id){
		String sql = "SELECT str.* FROM student_training_record str  WHERE str.selected_course_id = ?";
		return jdbcTemplate.query(sql, new Object[]{selected_course_id} , new StudentTrainingRecordRowMapper());
	}
	
	/**
	 * 查询课程调查具体内容
	 * @param settingId
	 * @return
	 */
	public List<OnlineExamBean> getCourseResearch(String settingId){
		String sql = "SELECT t.*  FROM training t where t.chapter_course_id = ? order by t.training_no";
		log.info(sql);
		List<OnlineExamBean> list = jdbcTemplate.query(sql ,new Object[]{settingId}, new OnlineExamRowMapper());
		return list;
	}
	
	
	public List<OnlineExamBean> getStudentSelectCourse(String userid ,String settingid , String type){
		String sql = "SELECT t.*,ri.choose FROM record_investigation ri "+
					" LEFT JOIN course_setting cs ON cs.id = ri.investigation_id "+
					" LEFT JOIN training t ON t.chapter_course_id = cs.id AND t.training_no = ri.training_id "+
					" WHERE ri.user_id = ? AND cs.id = ? AND cs.type = ?";
		return jdbcTemplate.query(sql, new Object[]{userid ,settingid ,type} , new OnlineExamRowMapper());
	}
	
	
	
	
	public List<OnlineExamBean> getCourseSettingReport(String settingId){
		StringBuffer buf = new StringBuffer();
		buf.append(" select tr.* ,group_concat(ri.choose) as choose from training tr ");
		buf.append(" left join record_investigation ri  ");
		buf.append(" on ri.investigation_id = ? and ri.training_id = tr.training_no  ");
		buf.append(" where tr.chapter_course_id = ? group by ri.training_id ");
		log.info(buf.toString());
		return jdbcTemplate.query(buf.toString() ,new Object[]{settingId,settingId}, new CourseSettingReportRowMapper());
	}
	
	
	
	public List<OnlineExamBean> getOnlineExamListByCourseId(String courseid,String selectedCourseId){
		String sql = "SELECT t.* , sc.id as selectCourseId  FROM training t "+
					" LEFT OUTER JOIN courses c ON c.id = t.chapter_course_id "+
					" LEFT OUTER JOIN selected_course sc ON sc.course_id = c.id and sc.student_id= ? and sc.status = 1 "+
					" WHERE c.id = ? ORDER BY t.training_no";
		log.info(sql);
		log.info("--------courseid="+courseid);
		log.info("--------userid="+CCPContants.getUserBean().getId());
		List<OnlineExamBean> list = jdbcTemplate.query(sql ,new Object[]{CCPContants.getUserBean().getId(),courseid}, new OnlineExamRowMapper());
		answerCheck(list, selectedCourseId);
		return list ;
	}
	
	public List<OnlineExamBean> getOnlineExamListByChapterTd(String chapterid){
		String sql = "SELECT t.* , sc.id as selectCourseId FROM training t "+
						" LEFT OUTER JOIN courses_detail cd ON cd.id = t.chapter_course_id "+
						" LEFT OUTER JOIN courses c ON c.id = cd.courses_id "+
						" LEFT OUTER JOIN selected_course sc ON sc.course_id = c.id and sc.student_id= ? and sc.status = 1"+
						" WHERE cd.id = ? ORDER BY t.training_no";
		List<OnlineExamBean> list = jdbcTemplate.query(sql ,new Object[]{CCPContants.getUserBean().getId(),chapterid}, new OnlineExamRowMapper());
		answerCheck(list,chapterid);
		return list ;
	}
	
	public void answerCheck(List<OnlineExamBean> list , String selected_course_id){
		List<StudentTrainingRecordBean> strBeanList = getStudentTrainingRecordList(selected_course_id);
		for (int i = 0; i < list.size(); i++) {
			OnlineExamBean bean = list.get(i);
			for(StudentTrainingRecordBean strBean : strBeanList){
				if(bean.getId().equals(strBean.getTraing_id())){
					bean.setSelectAnswer(strBean.getAnswer());
					list.set(i, bean);
				}
			}
		}
	}

	
	public boolean updateSelectCourseInfo(final String courseSelectId ,final String score ,final  String qualified){
		final int index = CCPContants.getClobalIndex() ;
		transactionTemplate.execute(new TransactionCallback() {
			public Object doInTransaction(TransactionStatus arg0) {
				try {
					String sql = "UPDATE selected_course SET score = ? , status = ? WHERE id = ?";
					Object[] objs = new Object[]{score, qualified ,courseSelectId};
					jdbcTemplate.update(sql,objs);
					
					sql = "DELETE FROM student_training_record WHERE selected_course_id = ?";
					jdbcTemplate.update(sql ,new Object[]{courseSelectId});
					CCPContants.tranmitResultMap.put(index, true);
				} catch (Exception e) {
					arg0.setRollbackOnly();
					log.error("selectValueChange failed : ", e);
					CCPContants.tranmitResultMap.put(index, false);
				}
				return null;
			}
		});
		return (Boolean) CCPContants.tranmitResultMap.get(index);
	}
	
	public class OnlineExamRowMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			OnlineExamBean bean = new OnlineExamBean();
			bean.setId(rs.getString("id"));
			bean.setChapter_course_id(rs.getString("chapter_course_id"));
			bean.setIndex(rs.getString("training_no"));
			bean.setTopic(rs.getString("training_contents"));
			bean.setScore(rs.getInt("training_score"));
			
			String training_options = rs.getString("training_options");
			
			JSONObject json = JSONObject.fromObject(training_options);
			String type = json.getString("type");
			if(type.equals("0")){
				//选择题
				bean.setShowSelect(true);
				bean.setShwoJudge(false);
				bean.setAnswerA(json.getString("a"));
				bean.setAnswerB(json.getString("b"));
				bean.setAnswerC(json.getString("c"));
				bean.setAnswerD(json.getString("d"));
			}else{
				//判断题
				bean.setShowSelect(false);
				bean.setShwoJudge(true);
			}
			
			
			
//			String[] strs = training_options.split(":");
//			if(strs[0].equals("0")){
//				//选择题
//				bean.setShowSelect(true);
//				bean.setShwoJudge(false);
//				bean.setAnswerA(strs[1]);
//				bean.setAnswerB(strs[2]);
//				bean.setAnswerC(strs[3]);
//				bean.setAnswerD(strs[4]);
//			}else{
//				//判断题
//				bean.setShowSelect(false);
//				bean.setShwoJudge(true);
//			}
			try {
				String v = rs.getString("choose");
				if(v != null)
					v = v.toUpperCase();
			 
				bean.setSelectAnswer(v);
			} catch (Exception e) {
				// TODO: handle exception
			}
			bean.setAnswer(rs.getString("training_refrence_answer"));
			try{
				bean.setSelectCourseId(rs.getInt("selectCourseId"));
			}catch(Exception e){};
			return bean;
		}
	}
	
	
	public class CourseSettingReportRowMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			OnlineExamBean bean = new OnlineExamBean();
			bean.setId(rs.getString("id"));
			bean.setChapter_course_id(rs.getString("chapter_course_id"));
			bean.setIndex(rs.getString("training_no"));
			bean.setTopic(rs.getString("training_contents"));
			bean.setScore(rs.getInt("training_score"));
			
			String training_options = rs.getString("training_options");
			String[] strs = training_options.split(":");
			if(strs[0].equals("0")){
				//选择题
				bean.setShowSelect(true);
				bean.setShwoJudge(false);
				bean.setAnswerA(strs[1]);
				bean.setAnswerB(strs[2]);
				bean.setAnswerC(strs[3]);
				bean.setAnswerD(strs[4]);
			}else{
				//判断题
				bean.setShowSelect(false);
				bean.setShwoJudge(true);
			}
			bean.setAnswer(rs.getString("training_refrence_answer"));
			try{
				bean.setSelectCourseId(rs.getInt("selectCourseId"));
			}catch(Exception e){};
			
			String choose = rs.getString("choose");
			String[] ary = choose.split(",");
			for(String r : ary){
//				log.info("-----------r = "+r);
				if("a".equalsIgnoreCase(r)){
					bean.setaNum();
				}else if("b".equalsIgnoreCase(r)){
					bean.setbNum();
				}else if("c".equalsIgnoreCase(r)){
					bean.setcNum();
				}else if("d".equalsIgnoreCase(r)){
					bean.setdNum();
				}
			}
			
			return bean;
		}
	}
	
	
	public class StudentTrainingRecordRowMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			StudentTrainingRecordBean bean = new StudentTrainingRecordBean();
			bean.setTraing_id(rs.getString("traing_id"));
			bean.setAnswer(rs.getString("answer"));
			bean.setScore(rs.getString("score"));
			return bean ;
		}
	}
}
